package com.broad.school_description.dao;

import com.broad.school_description.pojo.SchoolExample;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface GetSchoolExample {
    // 所有数据
    @Select("select t1.*,IFNULL(url,'') url from share_example.indi_result t1 " +
            "left outer join share_example.url_index t2 " +
            "on t1.id =t2.id ")
    List<SchoolExample> getSchoolExampleAll();
    // 所有数据的数量
    @Select("select count(*) from share_example.indi_result t1 " +
            "left outer join share_example.url_index t2 " +
            "on t1.id =t2.id ")
    Integer getSchoolExampleAllCount();

    // 自定义分页
    @Select("select t1.*,IFNULL(url,'') url from share_example.indi_result t1 " +
            "left outer join share_example.url_index t2 " +
            "on t1.id =t2.id " +
            "where t1.id>#{start_id} " +
            "and t1.id<=#{end_id}")
    List<SchoolExample> getSchoolExampleAllAccId(@Param("start_id") Integer startId,@Param("end_id") Integer endId);



    /**
     *
     * 动态sql，解决限制问题
     *
     * */
    // 双向选择的数据
    @Select("<script> select t1.* ,IFNULL(url,'') url from share_example.indi_result t1 " +
            "left outer join share_example.url_index t2 " +
            "on t1.id =t2.id " +
            "where " +
            "<if test='nation_area==\"不限\" and major==\"不限\"'> " +
            "1=1 " +
            "</if> " +
            "<if test='nation_area!=\"不限\" and major==\"不限\"'> " +
            "nation_area=#{nation_area} " +
            "</if> " +
            "<if test='nation_area==\"不限\" and major!=\"不限\"'> " +
            "major=#{major} " +
            "</if> " +
            "<if test='nation_area!=\"不限\" and major!=\"不限\"'> " +
            "nation_area=#{nation_area} and major=#{major} " +
            "</if> " +
            "</script> ")
    List<SchoolExample> getSelSchoolExample(String nation_area, String major);
    /**
     *
     * 动态sql，解决限制问题
     *
     * */
    // 双项选择的数据的数量
    @Select("<script> select count(*) from share_example.indi_result " +
            "" +
            "where " +
            "<if test='nation_area==\"不限\" and major==\"不限\"'> " +
            "1=1 " +
            "</if> " +
            "<if test='nation_area!=\"不限\" and major==\"不限\"'> " +
            "nation_area=#{nation_area} " +
            "</if> " +
            "<if test='nation_area==\"不限\" and major!=\"不限\"'> " +
            "major=#{major} " +
            "</if> " +
            "<if test='nation_area!=\"不限\" and major!=\"不限\"'> " +
            "nation_area=#{nation_area} and major=#{major} " +
            "</if> " +
            "</script> ")
    Integer getSelSchoolExampleCount(String nation_area, String major);





}
